package com.weibo.sql;

public class SqlFile {
	/**
	 * 游客列表sql
	 */
	public static final String HOST_VISITOR_DEAFAULT = "SELECT HOST.*,PRO.* FROM WEIBO_HOST_INFO HOST LEFT JOIN WEIBO_PROPERTIES_INFO PRO ON HOST.HOST_ID = PRO.PERSON_ID ";
	//	public static final String HOST_VISITOR_DEAFAULT = "SELECT OTHERTALE.*, TYP.* FROM ( SELECT	HOST.*,PRO.* FROM WEIBO_HOST_INFO HOST LEFT JOIN WEIBO_PROPERTIES_INFO PRO ON HOST.HOST_ID = PRO.PERSON_ID )OTHERTALE, WEIBO_HOST_TYPE TYP,	WEIBO_TYPE_HOST_CONNECT CONN WHERE OTHERTALE.HOST_ID LIKE CONN.HOS_ID AND CONN.TYP_ID LIKE TYP.TYP_ID ";
	
	/**
	 * 获取游客列表的总记录数
	 */
	public static final String HOST_VISITOR_DEAFAULT_TOTAL_SIZE = "SELECT COUNT(*) NUM FROM WEIBO_HOST_INFO HOST LEFT JOIN WEIBO_PROPERTIES_INFO PRO ON HOST.HOST_ID = PRO.PERSON_ID ORDER BY HOST.HOST_DATE";
	/**
	 * 粉丝量降序排序
	 */
	public static final String HOST_VISITOR_SORT_SORTFENSI_DESC = "SELECT HOST.*,PRO.* FROM WEIBO_HOST_INFO HOST LEFT JOIN WEIBO_PROPERTIES_INFO PRO ON HOST.HOST_ID = PRO.PERSON_ID ORDER BY TO_NUMBER(REPLACE( HOST.HOST_FS_NUMBER, '万', '0000')) DESC";
	/**
	 * 粉丝量升序排序
	 */
	public static final String HOST_VISITOR_SORT_SORTFENSI_ASC = "SELECT HOST.*,PRO.* FROM WEIBO_HOST_INFO HOST LEFT JOIN WEIBO_PROPERTIES_INFO PRO ON HOST.HOST_ID = PRO.PERSON_ID ORDER BY TO_NUMBER(REPLACE( HOST.HOST_FS_NUMBER, '万', '0000'))";
	/*
	 * 订单量按月降序排序
	 */
	public static final String HOST_VISITOR_SORT_SORTDINGDY_DESC = "SELECT HOST.*,PRO.* FROM WEIBO_HOST_INFO HOST LEFT JOIN WEIBO_PROPERTIES_INFO PRO ON HOST.HOST_ID = PRO.PERSON_ID ";
	/**
	 * 订单量按月升序排序
	 */
	public static final String HOST_VISITOR_SORT_SORTDINGDY_ASC = "SELECT HOST.*,PRO.* FROM WEIBO_HOST_INFO HOST LEFT JOIN WEIBO_PROPERTIES_INFO PRO ON HOST.HOST_ID = PRO.PERSON_ID ";
	/**
	 * 订单量按周降序排序
	 */
	public static final String HOST_VISITOR_SORT_SORTDINGDZ_DESC = "SELECT HOST.*,PRO.* FROM WEIBO_HOST_INFO HOST LEFT JOIN WEIBO_PROPERTIES_INFO PRO ON HOST.HOST_ID = PRO.PERSON_ID ";
	/*
	 * 订单量按周升序排序
	 * 
	 */
	public static final String HOST_VISITOR_SORT_SORTDINGDZ_ASC = "SELECT HOST.*,PRO.* FROM WEIBO_HOST_INFO HOST LEFT JOIN WEIBO_PROPERTIES_INFO PRO ON HOST.HOST_ID = PRO.PERSON_ID ";
	/**
	 * 转发价降序排序
	 */
	public static final String HOST_VISITOR_SORT_SORTZAFJ_DESC = "SELECT HOST.*,PRO.* FROM WEIBO_HOST_INFO HOST LEFT JOIN WEIBO_PROPERTIES_INFO PRO ON HOST.HOST_ID = PRO.PERSON_ID ORDER BY PRO.PERSON_ZHUANF_PRICE";
	/**
	 * 转发价升序排序
	 */
	public static final String HOST_VISITOR_SORT_SORTZAFJ_ASC = "SELECT HOST.*,PRO.* FROM WEIBO_HOST_INFO HOST LEFT JOIN WEIBO_PROPERTIES_INFO PRO ON HOST.HOST_ID = PRO.PERSON_ID  ORDER BY PRO.PERSON_ZHUANF_PRICE DESC";
	/**
	 * 直发价降序
	 */
	public static final String HOST_VISITOR_SORT_SORTZFJ_DESC = "SELECT HOST.*,PRO.* FROM WEIBO_HOST_INFO HOST LEFT JOIN WEIBO_PROPERTIES_INFO PRO ON HOST.HOST_ID = PRO.PERSON_ID  ORDER BY PRO.PRO.PERSON_ZHIF_PRICE";
	/**
	 * 直发价升序
	 */
	public static final String HOST_VISITOR_SORT_SORTZFJ_ASC = "SELECT HOST.*,PRO.* FROM WEIBO_HOST_INFO HOST LEFT JOIN WEIBO_PROPERTIES_INFO PRO ON HOST.HOST_ID = PRO.PERSON_ID ORDER BY PRO.PRO.PERSON_ZHIF_PRICE DESC ";
	/**
	 * 转评价降序
	 */
	public static final String HOST_VISITOR_SORT_SORTZHUANP_DESC = "SELECT HOST.*,PRO.* FROM WEIBO_HOST_INFO HOST LEFT JOIN WEIBO_PROPERTIES_INFO PRO ON HOST.HOST_ID = PRO.PERSON_ID  ORDER BY PRO.HOST_ZP_VALUE";
	/**
	 * 转评价升序
	 */
	public static final String HOST_VISITOR_SORT_SORTZHUANP_ASC = "SELECT HOST.*,PRO.* FROM WEIBO_HOST_INFO HOST LEFT JOIN WEIBO_PROPERTIES_INFO PRO ON HOST.HOST_ID = PRO.PERSON_ID  ORDER BY PRO.HOST_ZP_VALUE DESC";
	/**
	 * 红人列表
	 */
	public static final String HOST_HONGREN_LIST = "SELECT OTHERTALE.*, TYP.* FROM ( SELECT	HOST.*,PRO.* FROM WEIBO_HOST_INFO HOST LEFT JOIN WEIBO_PROPERTIES_INFO PRO ON HOST.HOST_ID = PRO.PERSON_ID )OTHERTALE, WEIBO_HOST_TYPE TYP,	WEIBO_TYPE_HOST_CONNECT CONN WHERE OTHERTALE.HOST_ID LIKE CONN.HOS_ID AND CONN.TYP_ID LIKE TYP.TYP_ID AND TYP.TYP_NAME LIKE '红人博主'";
	/**
	 * 名人列表
	 */
	public static final String HOST_MINGREN_LIST = "SELECT OTHERTALE.*, TYP.* FROM ( SELECT	HOST.*,PRO.* FROM WEIBO_HOST_INFO HOST LEFT JOIN WEIBO_PROPERTIES_INFO PRO ON HOST.HOST_ID = PRO.PERSON_ID )OTHERTALE, WEIBO_HOST_TYPE TYP,	WEIBO_TYPE_HOST_CONNECT CONN WHERE OTHERTALE.HOST_ID LIKE CONN.HOS_ID AND CONN.TYP_ID LIKE TYP.TYP_ID AND TYP.TYP_NAME LIKE '名人博主'";
	/**
	 * 重点推荐
	 */
	public static final String HOST_ZHONGDIANTUIJIAN_LIST = "SELECT OTHERTALE.*, TYP.* FROM ( SELECT	HOST.*,PRO.* FROM WEIBO_HOST_INFO HOST LEFT JOIN WEIBO_PROPERTIES_INFO PRO ON HOST.HOST_ID = PRO.PERSON_ID )OTHERTALE, WEIBO_HOST_TYPE TYP,	WEIBO_TYPE_HOST_CONNECT CONN WHERE OTHERTALE.HOST_ID LIKE CONN.HOS_ID AND CONN.TYP_ID LIKE TYP.TYP_ID AND TYP.TYP_NAME LIKE '重点推荐'";
	/**
	 * 热门博主
	 */
	public static final String HOST_REMEN_LIST = "SELECT OTHERTALE.*, TYP.* FROM ( SELECT	HOST.*,PRO.* FROM WEIBO_HOST_INFO HOST LEFT JOIN WEIBO_PROPERTIES_INFO PRO ON HOST.HOST_ID = PRO.PERSON_ID )OTHERTALE, WEIBO_HOST_TYPE TYP,	WEIBO_TYPE_HOST_CONNECT CONN WHERE OTHERTALE.HOST_ID LIKE CONN.HOS_ID AND CONN.TYP_ID LIKE TYP.TYP_ID AND TYP.TYP_NAME LIKE '热门博主'";
	
	/**
	 * 删除用户根据USER_ID
	 */
	public static final String USER_DELETE_BYID = "DELETE FROM WEIBO_USER WHERE USER_ID LIKE ?";
	/**
	 * 删除用户根据USER_NAME
	 */
	public static final String USER_DELETE_BYNAME = "DELETE FROM WEIBO_USER WHERE USER_NAME LIKE ?";
	
	/**
	 * 获取用户列表的总记录条数
	 */
	public static final String USER_LIST_TOTALSIZE = "SELECT COUNT(*) NUM FROM WEIBO_USER";
	
	/**
	 * 获取用户列表根据userID
	 */
	public static final String USER_SELECT_BYID = "SELECT * FROM WEIBO_USER WHERE USER_ID LIKE ?";
	/**
	 * 获取用户列表根据userNAME
	 */
	public static final String USER_SELECT_BYNAME = "SELECT * FROM WEIBO_USER WHERE USER_NAME LIKE ?";
	
	/**
	 * 获取用户列表
	 */
	public static final String USER_LIST = "SELECT * FROM WEIBO_USER";
	
	/**
	 * 获取用户账单列表
	 */
	public static final String BILLING_LIST = "SELECT USER_ID,USER_NAME,USER_COMPPANY,USER_YUE,USER_FREEZE FROM WEIBO_USER";
	
	/**
	 * 查询充值记录列表
	 */
	public static final String CHARGE_RECORD_LIST = "SELECT CHL_DATE, CHL_VLALUE, CHL_NAME FROM WEIBO_USER_CHONZHI_LOG";
	
	/**
	 * 插入用户信息
	 */
	public static final String USER_SAVE = "INSERT INTO WEIBO_USER VALUES(USER_INDEX.NEXTVAL,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	/**
	 * 更新用户信息
	 */
	public static final String USER_UPDATE = "UPDATE WEIBO_USER SET USER_NAME=?,USER_SEX=?,USER_PSW=?,USER_RENAME=?,USER_COMPPANY=?,USER_YUE=?,USER_FREEZE=?,USER_TYPE=?,USER_PHONE=?,USER_DUTY=?,USER_TELPHONE=?,USER_MAIL=?,USER_WEIBO_PATH=?,USER_DUTY_SORT=?,USER_CITY=?,USER_ORDER_SORT=?,USER_ORDER_TIME=?,USER_STATE=?,USER_EXIT_TIME=to_date(?,'YYYY-MM-DD   HH24:MI:SS'),STARTTIME=?,ENDTIME=? WHERE USER_ID=?";
	/**
	 * 插入用户充值信息
	 */
	public static final String USER_CHARGE_SAVE = "INSERT INTO WEIBO_USER_CHONZHI_LOG VALUES(USER_INDEX.NEXTVAL,to_date(?,'YYYY-MM-DD  HH24:MI:SS'),?,?)";

	/**
	 * 添加历史记录信息
	 */
	public static final String HISTORY_ADD = "INSERT INTO WEIBO_USER_HISTORY VALUES(HISTORY_INDEX.NEXTVAL,?,?,?,?)";
	/**
	 * 删除用户记录
	 */
	public static final String HISTORY_DELETE = "DELETE FROM WEIBO_USER_HISTORY WHERE HIS_ID LIKE ?";
	
	/**
	 * 获取用户列表
	 */
	public static final String HISTORY_SELECT = "SELECT INF.* FROM WEIBO_USER_HISTORY HIS,WEIBO_HOST_INFO INF WHERE INF.HOST_ID = HIS.HOST_ID AND HIS.USER_ID=?";
	
	/**
	 * 添加收藏夹
	 */
	public static final String FAVORITE_ADD = "INSERT INTO WEIBO_USER_FAVORITE VALUES(FAVORITE_INDEX.NEXTVAL,?,?,?,SYSDATE)";
	
	/**
	 * 删除收藏夹
	 */
	public static final String FAVORITE_DELETE = "DELETE FROM WEIBO_USER_FAVORITE WHERE FAV_ID LIKE ?";
	
	/**
	 * 获取收藏夹列表
	 */
	public static final String FAVORITE_SELECT = "SELECT INF.* FROM WEIBO_USER_FAVORITE FAV,WEIBO_HOST_INFO INF WHERE INF.HOST_ID = FAV.HOST_ID AND FAV.USER_ID =?";
	
	/**
	 * 判读收藏夹中用户的唯一性
	 */
	public static final String FAVORITE_UNIQUE = "SELECT * FROM WEIBO_USER_FAVORITE WHERE USER_ID LIKE ? AND HOST_ID LIKE ?";
	/**
	 * 添加分组信息
	 */
	public static final String GROUPE_ADD = "INSERT INTO WEIBO_GROUPE VALUES(GROUPE_INDEX.NEXTVAL,?,?,?)";
	
	/**
	 * 删除分组信息
	 */
	public static final String GROUPE_DELETE = "DELETE FROM WEIBO_GROUPE WHERE GRP_ID LIKE ?";
	
	/**
	 * 获取分组列表
	 */
	public static final String GROUPE_SELECT ="SELECT * FROM WEIBO_GROUPE";
	
	/**
	 * 添加分享任务信息
	 */
	public static final String FENX_TASK_ADD = "INSERT INTO WEIBO_FENX_TASK VALUES(FENX_INDEX.NEXTVAL,?,?,?,?,?,?,?,?,?,?)";
	
	/**
	 * 删除分享信息
	 */
	public static final String FENX_TASK_DELETE_BYID = "DELETE FROM WEIBO_FENX_TASK WHERE TAS_ID LIKE ?";
	/**
	 * 获取分享信息列表
	 */
	public static final String FENX_TASK_SELECT = "SELECT * FROM WEIBO_FENX_TASK";
	/**
	 * 更新分享信息
	 */
	public static final String FENX_TASK_UPDATE_BYID = "UPDATE SET WEIBO_FENX_TASK PLA_ID=?,TAS_TYPE=?,TAS_CONT_TYPE=?,TAS_NAME=?,TAS_START_TIME=?,FENX_HREF=?,TAS_OTHER=?,TAS_PROVE_IMG=?,TAS_STATE=?,HOST_IDS=? WHERE TAS_ID=?";
	
	
	/**
	 * 添加直发任务信息
	 */
	public static final String ZHIFA_TASK_ADD = "INSERT INTO WEIBO_ZHIF_TASK VALUES(ZHIF_INDEX.NEXTVAL,?,?,?,?,to_date(?,'YYYY-MM-DD   HH24:MI:SS'),?,?,?,?,?,?,?)";
	/**
	 * 获取直发任务的当前序列值
	 */
	public static final String ZHIFA_INDEX_CURRENT_VALUE = "SELECT MAX(ZHIF.TAS_ID) num FROM WEIBO_ZHIF_TASK ZHIF";
	
	/**
	 * 添加直发和任务类型的对应表
	 */
	public static final String ZHIFA_TASK_HOST = "INSERT INTO WEIBO_ZHIFA_TASK_HOST VALUES(TASK_HOST_INDEX.NEXTVAL,?,?)";
	
	/**
	 * 删除直发信息
	 */
	public static final String ZHIFA_TASK_DELETE_BYID = "DELETE FROM WEIBO_ZHIF_TASK WHERE TAS_ID LIKE ?";
	/**
	 * 获取直发信息列表
	 */
	public static final String ZHIFA_TASK_SELECT = "SELECT * FROM WEIBO_ZHIF_TASK";
	/**
	 * 更新直发信息
	 */
	public static final String ZHIFA_TASK_UPDATE_BYID = "UPDATE WEIBO_ZHIF_TASK SET PLA_ID=?,TAS_TYPE=?,TAS_CONT_TYPE=?,TAS_NAME=?,TAS_START_TIME=?,ZHIF_CONTENT=?,ZHIF_IMG=?,TAS_OTHER=?,TAS_PROVE_IMG=?,TAS_STATE=?,HOST_IDS=?,USER_ID=? WHERE TAS_ID=?";
	/**
	 * 获取任务列表的总条数
	 */
	public static final String ZHIFA_TASK_SIZE = "SELECT COUNT(*) NUM FROM WEIBO_ZHIF_TASK WHERE USER_ID LIKE ?";
	/**
	 * 添加直发任务信息
	 */
	public static final String ZHUANF_TASK_ADD = "INSERT INTO WEIBO_ZHUANF_TASK VALUES(ZHUANF_INDEX.NEXTVAL,?,?,?,?,?,?,?,?,?,?,?)";
	
	/**
	 * 删除直发信息
	 */
	public static final String ZHUANF_TASK_DELETE_BYID = "DELETE FROM WEIBO_ZHUANF_TASK WHERE TAS_ID LIKE ?";
	/**
	 * 获取直发信息列表
	 */
	public static final String ZHUANF_TASK_SELECT = "SELECT * FROM WEIBO_ZHUANF_TASK";
	/**
	 * 更新直发信息
	 */
	public static final String ZHUANF_TASK_UPDATE_BYID = "UPDATE WEIBO_ZHUANF_TASK SET PLA_ID=?,TAS_TYPE=?,TAS_CONT_TYPE=?,TAS_NAME=?,TAS_START_TIME=?,ZHUANF_HREF=?,ZHUANF_CONTENT=?,TAS_OTHER=?,TAS_PROVE_IMG=?,TAS_STATE=?,HOST_IDS=? WHERE TAS_ID=?";
	/**
	 * 登陆信息验证
	 */
	public static final String LOGIN_USER_VALIDATE = "SELECT * FROM WEIBO_USER WHERE USER_NAME LIKE ? AND USER_PSW LIKE ?";
	/**
	 * 更新用户登陆时间
	 */
	public static final String LOIGN_UPDATE_LOGINTIME = "UPDATE WEIBO_USER SET USER_EXIT_TIME=SYSDATE WHERE USER_ID LIKE ?";
	
	/**
	 * 获取用户按钮列表
	 */
	public static final String MENUS_GETLIST = "SELECT * FROM WEIBO_MENUS";
	
	/**
	 * 黑名单添加
	 */
	public static final String BLACKLIST_ADD = "INSERT INTO WEIBO_USER_BLACKLIST values(BLACKLIST_INDEX.NEXTVAL,?,?,?,SYSDATE) ";
	
	/**
	 * 黑名单删除
	 */
	public static final String BLACKLIST_DELETE = "DELETE FROM WEIBO_USER_BLACKLIST WHERE BLA_ID LIKE ?";
	
	/**
	 * 黑名单查询
	 */
	public static final String BLACKLIST_SEARCH="SELECT INF.* FROM WEIBO_USER_BLACKLIST BLA,WEIBO_HOST_INFO INF WHERE INF.HOST_ID = BLA.HOST_ID AND BLA.USER_ID = ?";
	
	/**
	 * 黑名单按照ID查询
	 */
	public static final String BLACKLIST_SELECT_BYID = "SELECT * FROM WEIBO_USER_BLACKLIST WHERE BLA_ID LIKE ?";
	
	/**
	 * 判断黑名单列表中是否存在
	 */
	public static final String BLACKLIST_ISEXIST = "SELECT * FROM WEIBO_USER_BLACKLIST WHERE USER_ID LIKE ? AND HOST_ID LIKE ?";
	
	/**
	 * 添加举报列表信息
	 */
	public static final String REPORT_ADD = "INSERT INTO WEIBO_REPORT VALUES(REPORT.INDEXVAL,?,?,?,SYSDATE,?)";
	
	/**
	 * 获取举报信息
	 */
	public static final String REPORT_SELECT = "SELECT * FROM WEIBO_REPORT";
	/**
	 * 删除举报信息
	 */
	public static final String REPORT_DELETE = "DELETE FROM WEIBO_REPORT WHERE REP_ID=?";
	
	/**
	 * 首页红人列表
	 */
	public static final String INITPAGE_HONGREN = "SELECT * FROM (SELECT * FROM WEIBO_HOST_INFO INF,WEIBO_HOST_TYPE TYP  WHERE INF.TYPE_ID = TYP.TYP_ID AND TYP.TYP_NAME LIKE '%红人%' ORDER BY HOST_DATE DESC) WHERE ROWNUM<=5";
	/**
	 * 首页名人列表
	 */
	public static final String INITPAGE_MINGREN = "SELECT * FROM (SELECT * FROM WEIBO_HOST_INFO INF,WEIBO_HOST_TYPE TYP  WHERE INF.TYPE_ID = TYP.TYP_ID AND TYP.TYP_NAME LIKE '%名人%' ORDER BY HOST_DATE DESC) WHERE ROWNUM<=5";
}
